package com.bskj.common.utils;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.DatabaseMetaData;

/**
 * 根据数据库自动生成dao，model，xml文件
 * @author wangliming
 *
 * @date 2019年1月7日上午11:53:28
 */
public class JavaBeanGenerator {

	private Connection connection;
	/* mysql url的连接字符串 */
	private static String url = "jdbc:mysql://47.106.157.167:3306/?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8";
	// 账号
	private static String user = "root";
	// 密码
	private static String password = "root123";
	
	private String driverClassName = "com.mysql.jdbc.Driver";
	
	// 数据库中的表名集合
	List<String> tableArr;
	
	// java类名集合
	List<String> javaNameArr;
	
	// 数据库中的表名
	String tableName = "t_park_info";
	
	// java类名
	String javaName = "ParkInfo";
	
	// javabean存放路径
	String filePath = "D:/tts9/workspace2018/kuaifuProject/common/src/main/java/com/bskj/common/bean/model/";
	// 数据库XML存放路径
	String XMLPath = "D:/tts9/workspace2018/kuaifuProject/common/src/main/java/com/bskj/common/dao/mapper/";
	// DAO层接口存放路径
	String DAOPath = "D:/tts9/workspace2018/kuaifuProject/common/src/main/java/com/bskj/common/dao/";
	// package 路径
	String packageDAO = "com.bskj.common.dao";
	String packagePath = "com.bskj.common.bean.model";
	
	// 数据库表的列名称
	private String[] colnames; // 列名数组
	
	// 列名类型数组
	private String[] colTypes;
	
	public static void main(String[] args) throws SQLException {
		JavaBeanGenerator bean = new JavaBeanGenerator();
		bean.doAction();
	}
	

	public JavaBeanGenerator() {
		try {// 驱动注册
			Class.forName(driverClassName);
			if (connection == null || connection.isClosed())
				// 获得链接
				connection = DriverManager.getConnection(url, user, password);
		} catch (ClassNotFoundException ex) {
			ex.printStackTrace();
			System.err.println("出现异常。。。");
		} catch (SQLException e) {
			e.printStackTrace();
			System.err.println("出现异常。。。");
		}
	}

	public Connection getConnection() {
		return connection;
	}

	public void setConnection(Connection connection) {
		this.connection = connection;
	}
	
	/**
	 * 根据表格名生成对应的dao，model，xml文件
	 * @throws SQLException
	 */
	public void doAction() throws SQLException {
		String sql = "select * from " + tableName;
		System.out.println(sql);
		PreparedStatement statement = connection.prepareStatement(sql);
		// 获取数据库的元数据
		ResultSetMetaData metadata = statement.getMetaData();
		// 数据库的字段个数
		int len = metadata.getColumnCount();
		StringBuffer str = new StringBuffer("");
		str.append("package " + packagePath + "; \r\n\n");
		// 字段名称
		colnames = new String[len + 1];
		// 字段类型 --->已经转化为java中的类名称了
		colTypes = new String[len + 1];
		for (int j = 1; j <= len; j++) {
			// System.out.println(metadata.getColumnName(i)+":"+metadata.getColumnTypeName(i)+":"+sqlType2JavaType(metadata.getColumnTypeName(i).toLowerCase())+":"+metadata.getColumnDisplaySize(i));
			// metadata.getColumnDisplaySize(i);
			colnames[j] =  sqlColumnName2JavaName(metadata.getColumnName(j)); // 获取字段名称
			colTypes[j] = sqlType2JavaType(metadata.getColumnTypeName(j)); // 获取字段类型
			System.out.println(colnames[j]);
			System.out.println(colTypes[j]);
		}
		
		str.append("import java.util.Date; \r\n");
		str.append("import com.zd.common.common.annotation.Column; \r\n");
		 
		
		// 校验
		if (null == colnames && null == colTypes)
			System.out.println(tableName + "没有字段名和类型");
		// 拼接
		
		createDAO(javaName);
		createXML(tableName, javaName);
		
		str.append("public class " + javaName + "  extends BaseModel  {\r\n");
		// 拼接属性
		for (int index = 1; index < colnames.length; index++) {
			str.append(getAttrbuteString(colnames[index], colTypes[index]));
		}
		// 拼接get，Set方法
		for (int index = 1; index < colnames.length; index++) {
			str.append(getGetMethodString(colnames[index], colTypes[index]));
			str.append(getSetMethodString(colnames[index], colTypes[index]));
		}
		str.append("}\r\n");
		// 输出到文件中
		File file = new File(filePath +  javaName + ".java");
		BufferedWriter write = null;

		try {
			write = new BufferedWriter(new FileWriter(file));
			write.write(str.toString());
			write.close();
		} catch (IOException e) {

			e.printStackTrace();
			if (write != null)
				try {
					write.close();
				} catch (IOException e1) {
					e1.printStackTrace();
				}
		}
	}
	
	/**
	 * 根据数据库连接生成所有表格的dao，model，xml文件
	 * @throws SQLException
	 */
	public void doActionsss() {
		try {
			
			DatabaseMetaData meta = (DatabaseMetaData) connection.getMetaData();
			ResultSet rs = meta.getTables(null, null, null,
			new String[] { "TABLE" });
			tableArr = new ArrayList<>();
			javaNameArr = new ArrayList<>();
			while (rs.next()) {
				tableArr.add(rs.getString(3));
				javaNameArr.add(sqlTableName2JavaName(rs.getString(3)));
			}
			System.out.println(tableArr);
			System.out.println(javaNameArr);
			
			
			for (int i = 0; i < tableArr.size(); i++) {
				String sql = "select * from " + tableArr.get(i);
				System.out.println(sql);
				PreparedStatement statement = connection.prepareStatement(sql);
				// 获取数据库的元数据
				ResultSetMetaData metadata = statement.getMetaData();
				// 数据库的字段个数
				int len = metadata.getColumnCount();
				StringBuffer str = new StringBuffer("");
				str.append("package " + packagePath + "; \r\n");
				// 字段名称
				colnames = new String[len + 1];
				// 字段类型 --->已经转化为java中的类名称了
				colTypes = new String[len + 1];
				for (int j = 1; j <= len; j++) {
					// System.out.println(metadata.getColumnName(i)+":"+metadata.getColumnTypeName(i)+":"+sqlType2JavaType(metadata.getColumnTypeName(i).toLowerCase())+":"+metadata.getColumnDisplaySize(i));
					// metadata.getColumnDisplaySize(i);
					colnames[j] =  sqlColumnName2JavaName(metadata.getColumnName(j)); // 获取字段名称
					colTypes[j] = sqlType2JavaType(metadata.getColumnTypeName(j)); // 获取字段类型
					System.out.println(colnames[j]);
					System.out.println(colTypes[j]);
				}
				
				str.append("import java.util.Date; \r\n");
				str.append("import com.zd.common.common.annotation.Column; \r\n");
				
				// 校验
				if (null == colnames && null == colTypes)
					System.out.println(tableArr.get(i) + "没有字段名和类型");
				// 拼接
				
				
				
				str.append("public class " + javaNameArr.get(i) + "  extends BaseModel  {\r\n");
				// 拼接属性
				for (int index = 1; index < colnames.length; index++) {
					str.append(getAttrbuteString(colnames[index], colTypes[index]));
				}
				// 拼接get，Set方法
				for (int index = 1; index < colnames.length; index++) {
					str.append(getGetMethodString(colnames[index], colTypes[index]));
					str.append(getSetMethodString(colnames[index], colTypes[index]));
				}
				str.append("}\r\n");
				// 输出到文件中
				File file = new File(filePath +  javaNameArr.get(i) + ".java");
				BufferedWriter write = null;

				try {
					write = new BufferedWriter(new FileWriter(file));
					write.write(str.toString());
					write.close();
				} catch (IOException e) {

					e.printStackTrace();
					if (write != null)
						try {
							write.close();
						} catch (IOException e1) {
							e1.printStackTrace();
						}
				}
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private String sqlTableName2JavaName(String columnName){
		String[] text = columnName.split("_");
		StringBuffer stringBuffer = new StringBuffer();
		for (int i = 1; i < text.length; i++) {
			
			String string = text[i].substring(0,1).toUpperCase();
			text[i] = text[i].replace(text[i].substring(0,1), string);
			stringBuffer.append(text[i]);
		}
		return stringBuffer.toString();
	}

	
	private String sqlColumnName2JavaName(String columnName){
		String[] text = columnName.split("_");
		StringBuffer stringBuffer = new StringBuffer();
		for (int i = 0; i < text.length; i++) {
			System.out.println(text[i]);
			if(i != 0){
				String string = text[i].substring(0,1).toUpperCase();
				text[i] = text[i].replace(text[i].substring(0,1), string);
			}
			stringBuffer.append(text[i]);
		}
		return stringBuffer.toString();
	}

	/**
	 * mysql的字段类型转化为java的类型
	 */
	private String sqlType2JavaType(String sqlType) {

		if (sqlType.equalsIgnoreCase("bit")) {
			return "boolean";
		} else if (sqlType.equalsIgnoreCase("tinyint")) {
			return "Integer";
		} else if (sqlType.equalsIgnoreCase("smallint")) {
			return "Integer";
		} else if (sqlType.equalsIgnoreCase("int")) {
			return "Integer";
		} else if (sqlType.equalsIgnoreCase("bigint")) {
			return "Long";
		} else if (sqlType.equalsIgnoreCase("float") || sqlType.equalsIgnoreCase("double") ) {
			return "Double";
		} else if (sqlType.equalsIgnoreCase("decimal") || sqlType.equalsIgnoreCase("numeric")
				|| sqlType.equalsIgnoreCase("real") || sqlType.equalsIgnoreCase("money")
				|| sqlType.equalsIgnoreCase("smallmoney")) {
			return "Double";
		} else if (sqlType.equalsIgnoreCase("varchar") || sqlType.equalsIgnoreCase("char")
				|| sqlType.equalsIgnoreCase("nvarchar") || sqlType.equalsIgnoreCase("nchar")
				|| sqlType.equalsIgnoreCase("text")) {
			return "String";
		} else if (sqlType.equalsIgnoreCase("datetime") || sqlType.equalsIgnoreCase("date")) {
			return "Date";
		} else if (sqlType.equalsIgnoreCase("image")) {
			return "Blod";
		} else if (sqlType.equalsIgnoreCase("timestamp")) {
			return "Timestamp";
		}else if (sqlType.equalsIgnoreCase("INTEGER")){
			return "Integer";
		}

		return null;
	}


	/**
	 * 获取字段字符串
	 */
	public StringBuffer getAttrbuteString(String name, String type) {
		if (!check(name, type)) {
			System.out.println("类中有属性或者类型为空");
			return null;
		}
		;
		String format = String.format("    private %s %s;\n\r", new String[] { type, name });
		return new StringBuffer(format);
	}

	/**
	 * 校验name和type是否合法
	 */
	public boolean check(String name, String type) {
		if ("".equals(name) || name == null || name.trim().length() == 0) {
			return false;
		}
		if ("".equals(type) || type == null || type.trim().length() == 0) {
			return false;
		}
		return true;

	}

	/**
	 * 获取get方法字符串
	 */
	private StringBuffer getGetMethodString(String name, String type) {
		if (!check(name, type)) {
			System.out.println("类中有属性或者类型为空");
			return null;
		}
		;
		String Methodname = "get" + GetTuoFeng(name);
		String format = String.format("\r    @Column\n    public %s %s(){\n", new Object[] { type, Methodname });
		format += String.format("        return this.%s;\r\n", new Object[] { name });
		format += "    }\r\n";
		return new StringBuffer(format);
	}

	/**
	 *  将名称首字符大写
	 * @param name
	 * @return
	 */
	private String GetTuoFeng(String name) {
		name = name.trim();
		if (name.length() > 1) {
			name = name.substring(0, 1).toUpperCase() + name.substring(1);
		} else {
			name = name.toUpperCase();
		}
		return name;
	}

	/**
	 * 获取字段的get方法字符串
	 */
	private Object getSetMethodString(String name, String type) {
		if (!check(name, type)) {
			System.out.println("类中有属性或者类型为空");
			return null;
		}
		;
		String Methodname = "set" + GetTuoFeng(name);
		String format = String.format("    public void %s(%s %s){\n", new Object[] { Methodname, type, name });
		format += String.format("        this.%s = %s;\r\n", new Object[] { name, name });
		format += "    }\r\n";
		return new StringBuffer(format);
	}
	
	/**
	 * xml文件
	 * @param tableName
	 * @param javaName
	 */
	private void createXML(String tableName , String javaName){
		File file = new File(XMLPath +  javaName + ".xml");
		StringBuffer xml = new StringBuffer();
		xml.append("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>"+"\r\n");
		xml.append("<!DOCTYPE mapper PUBLIC \"-//mybatis.org//DTD Mapper 3.0//EN\" \"http://mybatis.org/dtd/mybatis-3-mapper.dtd\" >"+"\r\n");
		xml.append("<mapper namespace=\"com.zd.common.dao."+javaName+"Dao\">"+"\r\n");
		xml.append("<select id=\"get\" resultType=\""+ javaName +"\">\r\nSELECT * FROM "+tableName+"r\n<include refid=\"common.where_id\"/>\r\n</select>\r\n"+"\r\n");
		xml.append("<select id=\"find\" resultType=\""+ javaName +"\">\r\nSELECT * FROM "+tableName+"\r\n<where>\r\n<include refid=\"common.where_param\" />\r\n</where>\r\n<include refid=\"common.order_param\" />\r\n<include refid=\"common.page_param\" />\r\n</select>\r\n"+"\r\n");
		xml.append("<insert id=\"insert\" keyProperty=\"id\" useGeneratedKeys=\"true\">\r\nINSERT INTO ${insertMap.className}(${insertMap.columnInsert}) VALUES (${insertMap.columnReturn})\r\n</insert>\r\n"+"\r\n");
		xml.append("<update id=\"update\">\r\nUPDATE ${insertMap.className} set ${insertMap.columnUpdate}\r\n<include refid=\"common.where_id\" />\r\n	</update>"+"\r\n");
		xml.append("<update id=\"del\">\r\nDELETE FROM "+tableName+"\r\n<include refid=\"common.where_id\" />\r\n</update>\r\n"+"\r\n");
		xml.append("<select id=\"count\" resultType=\"java.lang.Integer\">\r\nSELECT COUNT(*) FROM (\r\nSELECT * FROM "+tableName+"\r\n<where>\r\n<include refid=\"common.count_where_param\" />\r\n</where>\r\n) T\r\n</select>\r\n\r\n</mapper>"+"\r\n");
		
		BufferedWriter write = null;

		try {
			write = new BufferedWriter(new FileWriter(file));
			write.write(xml.toString());
			write.close();
		} catch (IOException e) {

			e.printStackTrace();
			if (write != null)
				try {
					write.close();
				} catch (IOException e1) {
					e1.printStackTrace();
				}
		}
		
	}
	
	/**
	 * dao 文件
	 * @param javaName
	 */
	private void createDAO(String javaName){
		File file = new File(DAOPath +  javaName + "Dao.java");
		StringBuffer dao = new StringBuffer();
		dao.append("package com.zd.common.dao;"+"\r\n\r\n");
		dao.append("import com.zd.common.bean.model."+ javaName +";"+"\r\n");
		dao.append("import com.zd.common.dao.common.BaseDao;"+"\r\n\r\n");
		dao.append("public interface "+ javaName +"Dao extends  BaseDao<"+ javaName +">{\r\n\r\n}"+"\r\n");
		
		BufferedWriter write = null;

		try {
			write = new BufferedWriter(new FileWriter(file));
			write.write(dao.toString());
			write.close();
		} catch (IOException e) {

			e.printStackTrace();
			if (write != null)
				try {
					write.close();
				} catch (IOException e1) {
					e1.printStackTrace();
				}
		}
		
		
	}

}
